<?php
class StoreManager
{
	
	public $storeId = -1;
	
public function getStoreList()
	{
		$result = array();
		$tmpStore = null;
		$addStore = false;
		$tmpTags = array();
		$rowCount = 0;
		$sql="select s.store_id, store, descr, address, city, state,
        zip, lat, active, lng, phone, website, twitter,
        email, facebook, yelp_term, short_review, rank, 
        is_list_item, map_group_id, map_descr, tags.tag 
  from stores s
left join (select st.store_id, t.tag from store_tags st, tag t where st.tag_id = t.tag_id) tags
on s.store_id = tags.store_id
		where s.active = 1		
order by 1";
    $q = mysql_query($sql);
    $totalRows = mysql_num_rows($q);
    while($row = mysql_fetch_assoc($q))
    {
    	
    	$rowCount++;
    	
    	if(is_null($tmpStore) || $row["store_id"] != $tmpStore->storeId){
    		
    		if(!is_null($tmpStore) && $row["store_id"] != $tmpStore->storeId){
    			$tmpStore->tags = $tmpTags;
    			array_push($result, $tmpStore);
    		}
    			
    		$tmpStore = new StoreVO2();
    		$tmpStore->storeId = $row["store_id"];
    		$tmpStore->name = $row["store"];
    		$tmpStore->descr = $row["descr"];
    		$tmpStore->address = $row["address"];
    		$tmpStore->city = $row["city"];
    		$tmpStore->state = $row["state"];
    		$tmpStore->zip = $row["zip"];
    		$tmpStore->phone = $row["phone"];
    		$tmpStore->twitter = $row["twitter"];
    		$tmpStore->facebook = $row["facebook"];
    		$tmpStore->lat = $row["lat"];
    		$tmpStore->lng = $row["lng"];
    		$tmpStore->isListItem = $row["is_list_item"];
    		$tmpStore->mapGroupId = $row["map_group_id"];
    		$tmpStore->mapDescr = $row["map_descr"];
    		$tmpStore->website = $row["website"];
    		$tmpTags = array();
    		array_push($tmpTags,$row["tag"]);
    		
    		if($rowCount == $totalRows){
    			$tmpStore->tags = $tmpTags;
    			array_push($result, $tmpStore);
    		}
    		
    	}else
    		array_push($tmpTags,$row["tag"]);
    	
    }
    return $result;
    
	}
	
	public function getTagList()
	{
		$result = array();
		$sql="select tag_id, tag from tag order by 2";
		$q = mysql_query($sql);
		while($row = mysql_fetch_assoc($q))
		{
			$tag = new TagVO();
			$tag->tagId = $row["tag_id"];
			$tag->tag = $row["tag"];
			array_push($result, $tag);
		}
		return $result;
	
	}
	
	public function getStoreTagList()
	{
		$result = array();
		$sql="select store_tag_id,t.tag, st.store_id  from store_tags st, tag t where t.tag_id = st.tag_id";
		$q = mysql_query($sql);
		while($row = mysql_fetch_assoc($q))
		{
			$storeTag = new StoreTagVO();
			$storeTag->storeTagId = $row["store_tag_id"];
			$storeTag->tag = $row["tag"];
			$storeTag->storeId = $row["store_id"];
			array_push($result, $storeTag);
		}
		return $result;
	
	}
	
	public function getSimpleStoreList()
	{
		$result = array();
		$sql="select store_id, store 
			from stores where active = true order by 2";
    $q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
    	$item = array(
    		'storeId' => $row["store_id"],
    		'name' => $row["store"]);
       array_push($result, $item);
    }
    return $result;
    
	}
	
	public function getStoresForYelp()
	{
		$result = array();
		$sql="select store_id, store, descr, address, city, state,
		zip, lat, active, lng, phone, website, twitter,
		email, facebook, yelp_term, short_review, rank, yelp_city
		from stores where active = 1 and store_id = 548	 order by ifnull(rank, 13), store";
		$q = mysql_query($sql);
		while($row = mysql_fetch_assoc($q))
		{
			$store = new StoreVO();
			$store->storeId = $row["store_id"];
			$store->name = $row["store"];
			$store->descr = $row["descr"];
			$store->address = $row["address"];
			$store->city = $row["city"];
			$store->state = $row["state"];
			$store->zip = $row["zip"];
			$store->lat = $row["lat"];
			$store->lng = $row["lng"];
			$store->phone = $row["phone"];
			$store->twitter = $row["twitter"];
			$store->facebook = $row["facebook"];
			$store->yelpTerm = $row["yelp_term"];
			$store->yelpCity = $row["yelp_city"];
			$store->shortReview = $row["short_review"];
			$store->yelpReviews =  $this->fetchYelpReviews($store->storeId);
			$store->labels = $this->getLabels($store->storeId);
			array_push($result, $store);
		}
		return $result;
	
	}
	
	
	public function getStores()
	{
		$result = array();
		$sql="select store_id, store, descr, address, city, state,
        zip, lat, active, lng, phone, website, twitter,
        email, facebook, yelp_term, short_review, rank, yelp_city 
				from stores where active = 1  order by ifnull(rank, 13), store";
    $q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
    	$store = new StoreVO();
      $store->storeId = $row["store_id"];
      $store->name = $row["store"];
      $store->descr = $row["descr"];
      $store->address = $row["address"];
      $store->city = $row["city"];
      $store->state = $row["state"];
      $store->zip = $row["zip"];
      $store->lat = $row["lat"];
      $store->lng = $row["lng"];
      $store->phone = $row["phone"];
      $store->twitter = $row["twitter"];
      $store->facebook = $row["facebook"];
      $store->yelpTerm = $row["yelp_term"];
      $store->yelpCity = $row["yelp_city"];
      $store->shortReview = $row["short_review"];
      //$store->yelpReviews =  $this->fetchYelpReviews($store->storeId);
      $store->labels = $this->getLabels($store->storeId);
      array_push($result, $store);
    }
    return $result;
    
	}
	
  public function getStore($storeId)
  {
  	$store = new StoreVO();
    $sql="select store_id, store, descr, address, city, state,
                zip, lat, active, lng, phone, website, twitter,
                email, facebook, yelp_term, short_review from stores where store_id= " . $storeId;
    $q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
      $store->storeId = $row["store_id"];
      $store->name = $row["store"];
      $store->descr = $row["descr"];
      $store->address = $row["address"];
      $store->city = $row["city"];
      $store->state = $row["state"];
      $store->zip = $row["zip"];
      $store->lat = $row["lat"];
      $store->lng = $row["lng"];
      $store->phone = $row["phone"];
      $store->twitter = $row["twitter"];
      $store->facebook = $row["facebook"];
      $store->yelpTerm = $row["yelp_term"];
      $store->shortReview = $row["short_review"];
      //array_push($result, $store);
    }
    return $store;
    
  }
  
  public function saveStore($storeVO) {
  	$sql="insert into stores
  			(store, address, city, state, zip, lat, lng, phone, active, website, twitter, facebook, yelp_term, email, rank, is_list_item, map_group_id)
  			values
  			('$storeVO->name', '$storeVO->address', '$storeVO->city', '$storeVO->state', '$storeVO->zip', 
  			'$storeVO->lat', '$storeVO->lng', '$storeVO->phone', '1', 
  		 '$storeVO->website', '$storeVO->twitter', '$storeVO->facebook', '$storeVO->yelpTerm' , '$storeVO->email', 3, 1, 0)";
  	//echo $sql;
  	mysql_query($sql);
  	$newId = $this->getLastId();
  	$sql = "update stores set map_group_id = store_id where store_id = " . $newId;
  	mysql_query($sql);
  	return $newId ;
  }
  
  public function saveStoreTag($storeId, $tagId) {
  	$sql="insert into store_tags
    			(store_id, tag_id)
    			values
    			($storeId,  $tagId)";
  	//echo $sql;
  	mysql_query($sql);
  	return $this->getLastId();
  }
  
  public function saveFilter($storeId, $filter) {
  	$sql="insert into result_filters
      			(store_id, filter)
      			values
      			($storeId, '$filter')";
  	//echo $sql;
  	mysql_query($sql);
  	return $this->getLastId();
  }
  
  
  
  public function getYelpReviews()
  {
  	if($this->storeId == -1)
     throw new Exception("Invalid StoreId");
     
   return $this->fetchYelpReviews($this->storeId );
    
  }
  
	public function getYelpReview($id) {
   return $this->fetchYelpReviews($id);
  }


  private function fetchYelpReviews($id)
  {
  	
    $result = array();
    $storeUrl="";
    $sql = "select mobile_url from yelp_store where store_id =$id";
  	$q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
      $storeUrl= $row["mobile_url"];
      break;
    }
    
    $sql=" select  date, excerpt, rating, trim(excerpt), rating_image_small_url, 
					    	 user_name, user_url, user_photo_url_small
					          from yelp_review
						where store_id =$id
						group by 
					date, excerpt, rating_image_small_url, 
					    	 user_name, user_url, user_photo_url_small
					order by 2 desc";
    $q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
      $review = new YelpReviewVO();
      $review->date = $row["date"];
      $review->excerpt= $row["excerpt"];
      $review->ratingImgURLSmall= $row["rating_image_small_url"];
      $review->rating= $row["rating"];
      $review->userPhotoURLSmall = $row["user_photo_url_small"];
      $review->userURL= $row["user_url"];
      $review->userName= $row["user_name"];
      $review->mobileURL = $storeUrl;
      array_push($result, $review);
    }
    return $result;
    
  }
  
 private function fetchArticles($id)
  {
    $result = array();
    $sql="select store_id, article_url, article_title, 
    			icon_url, image_url, article_domain, excerpt, source
    from articles where store_id = " . $id;
    $q = mysql_query($sql);
    while($row = mysql_fetch_assoc($q))
    {
      $article = new ArticleVO();
      $article->storeId = $id;
      $article->excerpt= $row["excerpt"];
      $article->articleUrl= $row["article_url"];
      $article->sourceIconUrl= $row["icon_url"];
      $article->image = $row["image_url"];
      $article->sourceURl= $row["article_domain"];
      $article->title= $row["article_title"];
      $article->source =$row["source"];
      if($article->source == "")
      	$article->source ="BLOG";
      array_push($result, $article);
    }
    return $result;
  }
  
  private function getLabels($id)
  {
    
    $idx=0;
    $result="";
    $sql="select t.tag as tag from store_tags st, tag t where st.tag_id = t.tag_id and st.store_id =  " . $id;
    //echo "id: " . $id . " sql: " . $sql . "<br>";
    $q = mysql_query($sql);
    while($row = mysql_fetch_row($q))
    {
      if($idx > 0)
        $result .= ", ";
      $result .= $row[0];
      $idx++;
      
    }
    return $result; 
  }
  
  private function getTags($id)
  {
    
    $idx=0;
    $result = array();
    $sql="select t.tag as tag from store_tags st, tag t where st.tag_id = t.tag_id and st.store_id =  " . $id;
    //echo "id: " . $id . " sql: " . $sql . "<br>";
    $q = mysql_query($sql);
    while($row = mysql_fetch_row($q))
    {
      array_push($result, $row[0]);
    }
    return $result; 
  }
  
  public function saveArticle($storeId, $articleUrl, $articleTitle, $iconUrl, $imageUrl, $articleDomain) {
  	
  	if(strlen($storeId) == 0 ||  strlen($articleUrl) == 0 || strlen($articleTitle) == 0 )
  	  return "Data for the $articleDomain article is incorrect";
  	  
  	$sql="insert into articles
		(store_id, article_url, article_title, icon_url, image_url, article_domain)
		values
		($storeId, '$articleUrl', '$articleTitle', '$iconUrl', '$imageUrl', '$articleDomain') ";
		mysql_query($sql);
		return "Data for the $articleDomain article has been saved";
  }
  
  public function getArticles($id) {
  	return $this->fetchArticles($id);
  }
  
	public function getYelps($id) {
	  $result = array();
  	$yelps = $this->fetchYelpReviews($id);
	  foreach ($yelps as $value) {
	  	$article = new ArticleVO();
      $article->storeId = $id;
      $article->excerpt= $value->excerpt;
      $article->articleUrl= "";
      $article->sourceIconUrl= "";
      $article->image = $value->userPhotoURLSmall;
      $article->sourceURl= "www.yelp.com";
      $article->title= "";
      $article->source ="YELP";
      $article->reviewDate = $value->date;
      $article->user = $value->userName;
      $article->userUrl = $value->userPhotoURLSmall;
      $article->ratingImage =$value->ratingImgURLSmall;
      $article->articleUrl = $value->mobileURL;
      array_push($result, $article);
		}
  	return $result;
  }
  
  private function getLastId() {
  	$newId = mysql_insert_id();
  	return $newId ;
  }
}
?>